package com.whzykj.ctrl;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;

import com.jfinal.core.Controller;
import com.jfinal.kit.HashKit;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.whzykj.common.DateUtil;
import com.whzykj.common.DbUtil;
import com.whzykj.common.SnUtil;
import com.whzykj.model.Account;
import com.whzykj.model.Dictionary;
import com.whzykj.model.GoodsInfo;
import com.whzykj.model.ReturnBill;
import com.whzykj.model.Userinfo;

public class ReturnController extends Controller{
	public void index(){
		this.renderJsp("");
	}
	
	/**
	 * 进入添加退赔页面
	 */
	public void addreturnbillbefore(){
		Integer custid=this.getParaToInt(0);			// 获取传递的客户编号
		String orderdate=this.getPara("order_date");	// 获取退赔订单日期
		if(orderdate==null || orderdate.trim().equals("")){
			orderdate=DateUtil.getYesterday();
		}
		String returnKind=ReturnBill.dao.getOptionsByKind("RETURN_KIND");			// 	退赔类型字符串
		String questionKind=ReturnBill.dao.getOptionsByKind("QUESTION_KIND");		//  问题类型字符串
		Record customer=ReturnBill.dao.getReturnCustomer(custid);					//  获取退赔客户相关信息
		String goodsList=ReturnBill.dao.getReturnGoodsInfoStr(orderdate, custid);	//	获取退赔客户当天采购商品信息
		this.setAttr("returnKind", returnKind);
		this.setAttr("questionKind", questionKind);
		this.setAttr("customer", customer);
		this.setAttr("goodsList", goodsList);
		this.setAttr("order_date", orderdate);
		this.renderJsp("/admin/system/return/return.jsp");
	}
	
	/**
	 * 添加退赔
	 */
	public void addreturnbill(){
		Userinfo userinfo = (Userinfo) this.getSessionAttr("LOGIN_USER");
		if (userinfo == null) {
			this.renderJsp("/login.jsp");
			return;
		}
		int userid=userinfo.getInt("userid");					// 获取当前登录用户的编号
		Integer custid=this.getParaToInt("cust_id");			// 获取传递的客户编号
		String orderdate=this.getPara("return_time");			// 获取退赔订单日期
		Integer acctid=this.getParaToInt("acct_id");			// 获取客户账号
		Account account=Account.dao.findById(acctid);
		boolean isCommit=true;
		if(account==null){
			this.setAttr("addMsg", "<span style='color:red;'>该客户账号信息不存在，请核实后再进行操作！</span>");
			isCommit=false;
		}
		String pass=this.getPara("password");
		if(pass==null || pass.trim().equals("")){
			this.setAttr("addMsg", "<span style='color:red;'>退赔密码不正确，请核对后再次输入！</span>");
			isCommit=false;
		}else{
			String password=HashKit.md5(pass);
			if(!password.equals(userinfo.getStr("password"))){
				this.setAttr("addMsg", "<span style='color:red;'>退赔密码不正确，请核对后再次输入！</span>");
				isCommit=false;
			}
		}
		if(!isCommit){
			String returnKind=ReturnBill.dao.getOptionsByKind("RETURN_KIND");			// 退赔类型字符串
			String questionKind=ReturnBill.dao.getOptionsByKind("QUESTION_KIND");		// 问题类型字符串
			Record customer=ReturnBill.dao.getReturnCustomer(custid);					// 获取退赔客户相关信息
			String goodsList=ReturnBill.dao.getReturnGoodsInfoStr(orderdate, custid);	// 获取退赔客户当天采购商品信息
			this.setAttr("returnKind", returnKind);
			this.setAttr("questionKind", questionKind);
			this.setAttr("customer", customer);
			this.setAttr("goodsList", goodsList);
			this.setAttr("order_date", orderdate);
			this.renderJsp("/admin/system/return/return.jsp");
			return;
		}
		
		if(orderdate==null || orderdate.trim().equals("")){
			orderdate=DateUtil.getYesterday();
		}
		
		String sn=SnUtil.getSn("RB");
		String create_time=DateUtil.getNow();
		Integer[] goodsids=this.getParaValuesToInt("goods_ids");						// 获取退赔商品编号
		String[] goods_prices=this.getParaValues("goods_prices");						// 获取退赔价格
		String[] returnKinds=this.getParaValues("returnKind");							// 获取退赔类型
		String[] questionKinds=this.getParaValues("questionKind");						// 获取退赔问题类型
		Integer[] nums=this.getParaValuesToInt("nums");									// 获取退赔件数
		String[] amounts=this.getParaValues("amount");									// 获取退赔金额
		double totalamounts=ReturnBill.dao.getReturnTotalsAmounts(orderdate, custid);	// 获取退赔客户退赔当天订单总金额
		
		List<String> sqlList=new ArrayList<String>();
		List<String> detailList=new ArrayList<String>();
		StringBuffer sql=null;
		int totalNum=0;				// 退赔总件数
		double totalAmount=0;		// 退赔总金额
		StringBuffer skind=new StringBuffer();		// 退赔类型字符串
		StringBuffer sgoodsid=new StringBuffer();	// 退赔的商品编号字符串
		if(goodsids!=null && goodsids.length>0){
			for(int i=0;i<goodsids.length;i++){		// 添加退赔明细到表中
				sql=new StringBuffer("INSERT INTO returndetails(sn,goods_id,goods_price,kind,num,amount,question_kind,create_time)");
				sql.append("VALUES('"+sn+"',"+goodsids[i]+","+Double.valueOf(goods_prices[i])+",'"+returnKinds[i]+"',");
				sql.append(nums[i]+","+Double.valueOf(amounts[i])+",'"+questionKinds[i]+"','"+create_time+"')");
				detailList.add(sql.toString());
				sql=null;
				totalNum=totalNum+nums[i];
				totalAmount=totalAmount+Double.valueOf(amounts[i]);
				if(i!=goodsids.length-1){
					sgoodsid.append(goodsids[i]+",");
					skind.append(returnKinds[i]+",");		// ??? questionKind - kind
				}else{
					sgoodsid.append(""+goodsids[i]);
					skind.append(""+returnKinds[i]);
				}
			}
		}
		if(totalamounts==0){
			totalamounts=totalAmount;
		}
		double r=totalAmount/totalamounts;
		BigDecimal b=new BigDecimal(r);  
		double rate=b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();  						// 计算退赔率，保留两位小数
		String pictureSrc=this.getPara("pictureSrc");												// 获取上传图片url
		String reason=this.getPara("reason");														// 获得退赔事由
		sql=new StringBuffer("INSERT INTO returnbill ");											// 添加到退赔流水表
		String orders=ReturnBill.dao.getReturnOrderIds(orderdate, custid,sgoodsid.toString());		// 获取退赔订单编号字符串
		sql.append("values('"+sn+"',"+custid+","+acctid+",'"+skind.toString()+"','"+sgoodsid.toString()+"',");
		sql.append("'"+orders+"',"+totalNum+","+totalAmount+",'"+reason+"',");
		sql.append("'"+pictureSrc+"',"+rate+",'01','"+orderdate+"',"+userid+",'"+create_time+"',"+userid+",'"+create_time+"')");
		sqlList.add(sql.toString());		// 1.保存到退赔账单表中
		sqlList.addAll(detailList);			// 2.保存到退赔明细表
		
		double pre_amount=account.getBigDecimal("amount").doubleValue();
		double amount=pre_amount+totalAmount;	// 退赔后总金额
		sql=new StringBuffer("INSERT INTO account_seq(sn,acct_id,seq_flag,change_type,pre_amount,amount,ref_sn,create_time)");
		sql.append("values('"+SnUtil.getSn("ZJ")+"',"+acctid+",'00','11',");
		sql.append(pre_amount+","+amount+",'"+sn+"','"+create_time+"')");
		sqlList.add(sql.toString());		// 3.保存到资金变动流水表中
		
		sql=new StringBuffer("update account set amount="+amount+",");
		sql.append("last_update_person="+userid+",last_update_time='"+create_time+"' ");
		sql.append(" where acct_id="+acctid);
		sqlList.add(sql.toString());		// 4.更新账户表中的金额信息
		sql=null;
		
		try{
			boolean res=DbUtil.batch(sqlList);
			if(res){
				this.setAttr("addMsg", "<span style='color:green'/>退赔成功！</span>");
				this.renderJsp("/admin/system/acct/acctlist.jsp");
				return;
			}else{
				this.setAttr("addMsg", "<span style='color:red;'>退赔失败，请核查数据是否输入正确！</span>");
			}
		}catch(Exception ex){
			ex.printStackTrace();
			this.setAttr("addMsg", "<span style='color:red;'>退赔失败，请核查数据是否输入正确！</span>");
		}
		
		String returnKind=ReturnBill.dao.getOptionsByKind("RETURN_KIND");			// 退赔类型字符串
		String questionKind=ReturnBill.dao.getOptionsByKind("QUESTION_KIND");		// 问题类型字符串
		Record customer=ReturnBill.dao.getReturnCustomer(custid);					// 获取退赔客户相关信息
		String goodsList=ReturnBill.dao.getReturnGoodsInfoStr(orderdate, custid);	// 获取退赔客户当天采购商品信息
		this.setAttr("returnKind", returnKind);
		this.setAttr("questionKind", questionKind);
		this.setAttr("customer", customer);
		this.setAttr("goodsList", goodsList);
		this.setAttr("order_date", orderdate);
		this.renderJsp("/admin/system/return/return.jsp");
	}
	
	/**
	 * 查询退赔订单日期指定商品的最大价格
	 */
	public void findGoodsPrice(){
		String orderdate=this.getPara("order_date");
		int custid=this.getParaToInt("custid");
		int goodsid=this.getParaToInt("goodsid");
		String price=ReturnBill.dao.getMaxPriceForReturn(orderdate, custid, goodsid);
		this.renderText(price);
	}
	
	/**
	 * 查询指定订单日期的对应客户的商品信息列表
	 */
	public void findGoodsList(){
		String orderdate=this.getPara("order_date");
		int custid=this.getParaToInt("custid");
		String goodsList=ReturnBill.dao.getReturnGoodsInfoStr(orderdate, custid);
		this.renderText(goodsList);
	}
	
	/**
	 * 进入到退赔记录页面
	 */
	public void returnrecordbefore(){
		String currDate=DateUtil.getToday();
		this.setAttr("currDate", currDate);
		this.renderJsp("/admin/system/return/returnrecord.jsp");
	}
	
	/**
	 * 查询退赔记录列表
	 */
	public void returnrecord(){
		Integer page = this.getParaToInt("page");
		Integer rows = this.getParaToInt("rows");
		String startDate=this.getPara("startDate");
		String endDate=this.getPara("endDate");
		Integer stationid=this.getParaToInt("stationid");
		String custname=this.getPara("custname");
		List<Record> recList = null;
		StringBuffer condition = new StringBuffer("where rb.state='01' ");
		if (startDate == null || startDate.trim().equals("")) {
			startDate = DateUtil.getToday();
		}
		if (endDate == null || endDate.trim().equals("")) {
			endDate = DateUtil.getToday();
		}
		condition.append("AND TO_DAYS(rb.apply_time)>=TO_DAYS('" + startDate
				+ "') AND TO_DAYS(rb.apply_time)<=TO_DAYS('" + endDate + "') ");
		if (stationid != null && stationid.intValue() != -1) {
			condition
					.append("AND sta.STATION_ID=" + stationid.intValue() + " ");
		}
		if (custname != null && !custname.trim().equals("")) {
			condition.append("AND cust.CUST_NAME LIKE '%" + custname.trim()
					+ "%' ");
		}
		StringBuffer sqlBefore=new StringBuffer("select rb.sn,rb.apply_time,rb.order_date,rb.amount,rb.num,rb.apply_user,rb.cust_id,cust.CUST_NAME,");
		sqlBefore.append("cust.MOBILE,rb.kind,sta.STATION_NAME,ui.USERNAME ");
		StringBuffer sqlAfter=new StringBuffer("from returnbill rb INNER JOIN customer cust on cust.CUST_ID=rb.cust_id ");
		sqlAfter.append("INNER JOIN station sta on sta.STATION_ID=cust.STATION_ID ");
		sqlAfter.append("inner JOIN userinfo ui on ui.USERID=rb.admit_user ");
		sqlAfter.append(condition.toString());
		sqlAfter.append("order by rb.apply_time DESC,sta.STATION_NAME,rb.kind,cust.CUST_ID");
		Page<ReturnBill> paginate = ReturnBill.dao.paginate(page, rows, sqlBefore.toString(), sqlAfter.toString());
		Map map = new HashMap();
		map.put("rows", ReturnBill.dao.dealReturnBillList(paginate.getList()));
		map.put("total", paginate.getTotalRow());
		this.renderJson(map);
	}
	
	/**
	 * 进入到退赔详情页面
	 */
	public void returndetails(){
		String sn=this.getPara(0);
		Record rec=ReturnBill.dao.findReturnRecordInfo(sn);
		this.setAttr("rec", rec);
		this.setAttr("sn", sn);
		this.renderJsp("/admin/system/return/returndetails.jsp");
	}
	
	/**
	 * 返回退赔详情列表
	 */
	public void returnitemdetails(){
		String sn=this.getPara("sn");
		List<Record> recList=ReturnBill.dao.findReturnRecordDetails(sn);
		Map map = new HashMap();
		map.put("rows", recList);
		map.put("total", recList.size());
		this.renderJson(map);
	}
	
	/**
	 * 进入到退赔报表（按站点）页面
	 */
	public void liststationreturnbefore(){
		String currDate=DateUtil.getYesterday();
		this.setAttr("currDate", currDate);
		this.renderJsp("/admin/system/return/liststationreturn.jsp");
	}
	
	/**
	 * 查询退赔报表（按站点）
	 */
	public void liststationreturn(){
		String startDate=this.getPara("startDate");
		String endDate=this.getPara("endDate");
		Integer stationid=this.getParaToInt("stationid");
		String questionkind=this.getPara("questionkind");
		if (startDate == null || startDate.trim().equals("")) {
			startDate = DateUtil.getToday();
		}
		if (endDate == null || endDate.trim().equals("")) {
			endDate = DateUtil.getToday();
		}
		List<Record> recList=ReturnBill.dao.findReturnListByStation(startDate, endDate, stationid);
		Map map = new HashMap();
		map.put("rows", recList);
		map.put("total", recList.size());
		this.renderJson(map);
	}
	
	/**
	 * 进入到退赔报表（按sku）页面
	 */
	public void listskureturnbefore(){
		String currDate=DateUtil.getYesterday();
		this.setAttr("currDate", currDate);
		this.renderJsp("/admin/system/return/listskureturn.jsp");
	}
	/**
	 * 查询退赔报表（按sku）
	 */
	public void listskureturn(){
		String startDate=this.getPara("startDate");
		String endDate=this.getPara("endDate");
		Integer goodsid=this.getParaToInt("goodsid");
		String questionkind=this.getPara("questionkind");
		if (startDate == null || startDate.trim().equals("")) {
			startDate = DateUtil.getYesterday();
		}
		if (endDate == null || endDate.trim().equals("")) {
			endDate = DateUtil.getYesterday();
		}
		if(questionkind==null || questionkind.trim().equals("")){
			questionkind="-1";
		}
		List<Record> recList=ReturnBill.dao.findReturnListBySku(startDate, endDate, goodsid, questionkind);
		Map map = new HashMap();
		map.put("rows", recList);
		map.put("total", recList.size());
		this.renderJson(map);
	}
	
	/**
	 * 查询所有的菜品信息
	 */
	public void findAllGoods(){
		List<GoodsInfo> list=GoodsInfo.me.find("SELECT goods_id,APP_NAME from goodsinfo");
		GoodsInfo goods=new GoodsInfo();
		goods.set("goods_id", -1).set("app_name", "- 全部 -");
		list.add(0, goods);
		this.renderJson(list);
	}
	
	/**
	 * 查询所有的问题类型
	 */
	public void findAllQuestion(){
		List<Dictionary> list=Dictionary.me.find("select dict_code,dict_name from dictionary where dict_type='QUESTION_KIND'");
		Dictionary dict=new Dictionary();
		dict.set("dict_code", "-1").set("dict_name", "- 全部 -");
		list.add(0, dict);
		this.renderJson(list);
	}
	
	/**
	 * 导出退赔记录到Excel
	 */
	public void exportReturnBillList(){
		Userinfo userinfo = (Userinfo) this.getSessionAttr("LOGIN_USER");
		if (userinfo == null) {
			this.renderJsp("/login.jsp");
			return;
		}
		String startDate=this.getPara("startDate");
		String endDate=this.getPara("endDate");
		Integer stationid=this.getParaToInt("stationid");
		String custname=this.getPara("custname");
		StringBuffer condition = new StringBuffer("where rb.state='01' ");
		if (startDate == null || startDate.trim().equals("")) {
			startDate = DateUtil.getToday();
		}
		if (endDate == null || endDate.trim().equals("")) {
			endDate = DateUtil.getToday();
		}
		condition.append("AND TO_DAYS(rb.apply_time)>=TO_DAYS('" + startDate
				+ "') AND TO_DAYS(rb.apply_time)<=TO_DAYS('" + endDate + "') ");
		if (stationid != null && stationid.intValue() != -1) {
			condition
					.append("AND sta.STATION_ID=" + stationid.intValue() + " ");
		}
		if (custname != null && !custname.trim().equals("")) {
			condition.append("AND cust.CUST_NAME LIKE '%" + custname.trim()
					+ "%' ");
		}
		StringBuffer sqlBefore=new StringBuffer("select rb.sn,rb.apply_time,rb.order_date,rb.amount,rb.num,rb.apply_user,rb.cust_id,cust.CUST_NAME,");
		sqlBefore.append("cust.MOBILE,rb.kind,sta.STATION_NAME,ui.USERNAME ");
		StringBuffer sqlAfter=new StringBuffer("from returnbill rb INNER JOIN customer cust on cust.CUST_ID=rb.cust_id ");
		sqlAfter.append("INNER JOIN station sta on sta.STATION_ID=cust.STATION_ID ");
		sqlAfter.append("inner JOIN userinfo ui on ui.USERID=rb.admit_user ");
		sqlAfter.append(condition.toString());
		sqlAfter.append("order by rb.apply_time DESC,sta.STATION_NAME,rb.kind,cust.CUST_ID");
		List<ReturnBill> nList=ReturnBill.dao.find(sqlBefore.toString()+sqlAfter.toString());
		nList=ReturnBill.dao.dealReturnBillList(nList);
		if(nList!=null && nList.size()>0){
			for(ReturnBill rec:nList){
				rec.set("amount", rec.getStr("amount").replace("￥", ""));
				rec.set("num", rec.getStr("num").replaceAll("件",""));
			}
		}
		
		String[] header = { "退赔操作时间", "退赔订单时间", "服务站", "退赔类型","客户姓名","手机号","退赔金额","退赔件数","操作人" };
		String[] columns={"apply_time","order_date","station_name","kind","cust_name","mobile","amount","num","username"};
		 // 创建工作薄  (换行 /r/n)
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();  
        // sheet:一张表的简称  
        // row:表里的行  
        // 创建工作薄中的工作表  
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("returnlist");
        // 单元格样式：正文内容
        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); 
        HSSFFont f=hssfWorkbook.createFont();
        f.setFontName("微软雅黑");
        f.setFontHeightInPoints((short)9);
        cellStyle.setFont(f);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setWrapText(true);//先设置为自动换行
        // 单元格样式：加粗显示
        HSSFCellStyle cellStyleBold = hssfWorkbook.createCellStyle(); 
        HSSFFont bf=hssfWorkbook.createFont();
        bf.setFontName("微软雅黑");
        bf.setFontHeightInPoints((short)9);
        bf.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cellStyleBold.setFont(bf);        
        cellStyleBold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyleBold.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyleBold.setWrapText(true);//先设置为自动换行
        // 用于格式化单元格的数据
//        HSSFDataFormat format = hssfWorkbook.createDataFormat();
        // 创建行  
        HSSFRow row = hssfSheet.createRow(0);
        row.setHeightInPoints(27);  
        // 创建单元格，设置表头 创建列  
        HSSFCell cell = null;  
        //便利表头  
        for (int i = 0; i < header.length; i++) {  
            //创建传入进来的表头的个数  
            cell = row.createCell(i);  
            //表头的值就是传入进来的值  
            cell.setCellValue(header[i]);
            cell.setCellStyle(cellStyleBold);	// 为单元格添加样式
        } 
        // 设置列宽
        hssfSheet.setColumnWidth(0, 20 * 256); 
        hssfSheet.setColumnWidth(1, 18 * 256);
        hssfSheet.setColumnWidth(2, 18 * 256); 
        hssfSheet.setColumnWidth(3, 28 * 256);
        hssfSheet.setColumnWidth(4, 12 * 256);
        hssfSheet.setColumnWidth(5, 16 * 256);
        hssfSheet.setColumnWidth(6, 12 * 256);
        hssfSheet.setColumnWidth(7, 12 * 256);
        hssfSheet.setColumnWidth(8, 12 * 256);
        cellStyle.setFont(f);
        try { 
        	int count=0;
            if(nList!=null && nList.size()>0){
            	for(ReturnBill rec:nList){
            		row = hssfSheet.createRow(++count);
            		row.setHeightInPoints(27); 
            		for (int i = 0; i < header.length; i++) {           			
            			cell=row.createCell(i);
            			cell.setCellStyle(cellStyle);	// 为单元格添加样式
//            			cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString());
            			if(i==6 || i==7){
            				try {
    							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//
    							cell.setCellValue(Double.valueOf(rec.get(columns[i]).toString()));
    						} catch (Exception e) {
    							// TODO Auto-generated catch block
    							e.printStackTrace();
    						} 
            			}else if(i==0){
            				cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString().substring(0, 19));
            			}else{
            				cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString());
            			}			
            		}
            	}
            }             
            HttpServletRequest request=this.getRequest();
            HttpServletResponse response=this.getResponse();
//            String filename="order.xls"; 
            String filename="退赔记录报表"+DateUtil.getDateTime("yyyyMMddHHmmss", new Date())+".xls";
            response.setContentType("application/vnd.ms-excel");   
            response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "iso8859-1")); //设置文件头编码方式和文件名
            OutputStream ouputStream = response.getOutputStream();   
            hssfWorkbook.write(ouputStream);   
            ouputStream.flush();   
            ouputStream.close();  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }
	}
	
	/**
	 * 导出退赔报表（按SKU）到Excel
	 */
	public void exportReturnListBySku(){
		Userinfo userinfo = (Userinfo) this.getSessionAttr("LOGIN_USER");
		if (userinfo == null) {
			this.renderJsp("/login.jsp");
			return;
		}
		String startDate=this.getPara("startDate");
		String endDate=this.getPara("endDate");
		Integer goodsid=this.getParaToInt("goodsid");
		String questionkind=this.getPara("questionkind");
		if (startDate == null || startDate.trim().equals("")) {
			startDate = DateUtil.getYesterday();
		}
		if (endDate == null || endDate.trim().equals("")) {
			endDate = DateUtil.getYesterday();
		}
		if(questionkind==null || questionkind.trim().equals("")){
			questionkind="-1";
		}
		List<Record> nList=ReturnBill.dao.findReturnListBySku(startDate, endDate, goodsid, questionkind);
		int totalnums=0,totalccnums=0,totalzjnums=0,temp=0;
		double totalamounts=0,totalccamounts=0,totalzjamounts=0;
		if(nList!=null && nList.size()>0){
			for(Record rec:nList){
				rec.set("goods_name", rec.getStr("goods_name")+"\r\n"+rec.getStr("date"));			
				rec.set("orderamounts", rec.getStr("orderamounts").replaceAll("￥", ""));
				rec.set("ccnums", rec.getStr("ccnums").replaceAll("件", ""));
				rec.set("ccamounts", rec.getStr("ccamounts").replaceAll("￥", ""));
				rec.set("zjnums", rec.getStr("zjnums").replaceAll("件", ""));
				rec.set("zjamounts", rec.getStr("zjamounts").replaceAll("￥", ""));
				if(rec.getStr("ordernums")!=null && !rec.getStr("ordernums").equals("合计")){
					rec.set("ordernums", rec.getStr("ordernums").replaceAll("件", ""));
					temp=Integer.valueOf(rec.get("ordernums").toString());	
					totalccnums=totalccnums+Integer.valueOf(rec.get("ccnums").toString());
					totalccamounts=totalccamounts+Double.valueOf(rec.get("ccamounts").toString());
					totalzjnums=totalzjnums+Integer.valueOf(rec.get("zjnums").toString());
					totalzjamounts=totalzjamounts+Double.valueOf(rec.get("zjamounts").toString());
				}else{
					totalamounts=totalamounts+Double.valueOf(rec.get("orderamounts").toString());
					totalnums=totalnums+temp;
				}
			}
			DecimalFormat df=new DecimalFormat("#0.0");
			Record r=new Record();
			r.set("goods_name", "汇总合计").set("ordernums", totalnums+"").set("orderamounts", totalamounts+"").set("question_kinds", "").set("ccnums", totalccnums+"");
			r.set("ccamounts", totalccamounts+"").set("ccrate", df.format(totalccamounts*100/totalamounts)+"%").set("zjnums", totalzjnums+"");
			r.set("zjamounts", totalzjamounts+"").set("zjrate", df.format(totalzjamounts*100/totalamounts)+"%");
			nList.add(r);
			
		}
		String[] header = { "SKU","总销售件数", "总销售金额","问题类型","残次退赔","残次退赔","残次退赔","整件退赔","整件退赔","整件退赔" };
		String[] header1 = { "SKU","总销售件数", "总销售金额","问题类型","退赔件数","退赔金额","退赔率","退赔件数","退赔金额","退赔率" };
		String[] columns={"goods_name","ordernums","orderamounts","question_kinds","ccnums","ccamounts","ccrate","zjnums","zjamounts","zjrate"};
		 // 创建工作薄  (换行 /r/n)
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();  
        // sheet:一张表的简称  
        // row:表里的行  
        // 创建工作薄中的工作表  
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("returnlist");
        // 单元格样式：正文内容
        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); 
        HSSFFont f=hssfWorkbook.createFont();
        f.setFontName("微软雅黑");
        f.setFontHeightInPoints((short)9);
        cellStyle.setFont(f);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setWrapText(true);//先设置为自动换行
        // 单元格样式：加粗显示
        HSSFCellStyle cellStyleBold = hssfWorkbook.createCellStyle(); 
        HSSFFont bf=hssfWorkbook.createFont();
        bf.setFontName("微软雅黑");
        bf.setFontHeightInPoints((short)9);
        bf.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cellStyleBold.setFont(bf);        
        cellStyleBold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyleBold.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyleBold.setWrapText(true);//先设置为自动换行
        // 用于格式化单元格的数据
//        HSSFDataFormat format = hssfWorkbook.createDataFormat();
        // 创建行  
        HSSFRow row = hssfSheet.createRow(0);
        row.setHeightInPoints(27);  
        // 创建单元格，设置表头 创建列  
        HSSFCell cell = null;  
        //便利表头  
        for (int i = 0; i < header.length; i++) {  
            //创建传入进来的表头的个数  
            cell = row.createCell(i);  
            //表头的值就是传入进来的值  
            cell.setCellValue(header[i]);
            cell.setCellStyle(cellStyleBold);	// 为单元格添加样式
        }
        row = hssfSheet.createRow(1);
        row.setHeightInPoints(27);   
        //便利表头  
        for (int i = 0; i < header1.length; i++) {  
            //创建传入进来的表头的个数  
            cell = row.createCell(i);  
            //表头的值就是传入进来的值  
            cell.setCellValue(header1[i]);
            cell.setCellStyle(cellStyleBold);	// 为单元格添加样式
        } 
        // 设置列宽
        hssfSheet.setColumnWidth(0, 30 * 256); 
        hssfSheet.setColumnWidth(1, 18 * 256);
        hssfSheet.setColumnWidth(2, 18 * 256); 
        hssfSheet.setColumnWidth(3, 28 * 256);
        hssfSheet.setColumnWidth(4, 12 * 256);
        hssfSheet.setColumnWidth(5, 16 * 256);
        hssfSheet.setColumnWidth(6, 12 * 256);
        hssfSheet.setColumnWidth(7, 12 * 256);
        hssfSheet.setColumnWidth(8, 12 * 256);
        hssfSheet.setColumnWidth(9, 12 * 256);
        try { 
        	int count=1;
        	boolean isCellTotal=false;
            if(nList!=null && nList.size()>0){
            	for(Record rec:nList){
            		row = hssfSheet.createRow(++count);
            		row.setHeightInPoints(27);
            		isCellTotal=(rec.getStr("ordernums")!=null && rec.getStr("ordernums").equals("合计") || rec.getStr("goods_name").equals("汇总合计"));
            		for (int i = 0; i < header.length; i++) {           			
            			cell=row.createCell(i);
            			if(isCellTotal) cell.setCellStyle(cellStyleBold);
            			else cell.setCellStyle(cellStyle);
//            			cell.setCellStyle(cellStyle);	// 为单元格添加样式
//            			cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString());
            			if(i==2 || i==4 || i==5 || i==7 || i==8){
            				try {
    							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//
    							cell.setCellValue(Double.valueOf(rec.get(columns[i]).toString()));
    						} catch (Exception e) {
    							e.printStackTrace();
    						} 
            			}else{
            				cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString());
            			}		
            		}
            	}
            }  
            
            // 设置合并单元格
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 6));
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 9));
            if(nList!=null && nList.size()>1){
            	Record r1=nList.get(0);
            	int startIndex=2;	// 0+1
            	int endIndex=2;
            	for(int i=1,size=nList.size();i<size;i++){
            		Record r2=nList.get(i);
            		if(!r2.getStr("goods_name").equals(r1.getStr("goods_name"))){
            			endIndex=i+1;
            			hssfSheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, 0, 0));            			
            			startIndex=i+2;
            		}
            		r1=nList.get(i);
            	}
            }
            
            HttpServletRequest request=this.getRequest();
            HttpServletResponse response=this.getResponse();
//            String filename="order.xls"; 
            String filename="退赔报表（按SKU）"+DateUtil.getDateTime("yyyyMMddHHmmss", new Date())+".xls";
            response.setContentType("application/vnd.ms-excel");   
            response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "iso8859-1")); //设置文件头编码方式和文件名
            OutputStream ouputStream = response.getOutputStream();   
            hssfWorkbook.write(ouputStream);   
            ouputStream.flush();   
            ouputStream.close();  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }
	}
	
	/**
	 * 导出退赔报表（按站）到Excel
	 */
	public void exportReturnListByStation(){
		Userinfo userinfo = (Userinfo) this.getSessionAttr("LOGIN_USER");
		if (userinfo == null) {
			this.renderJsp("/login.jsp");
			return;
		}
		String startDate=this.getPara("startDate");
		String endDate=this.getPara("endDate");
		Integer stationid=this.getParaToInt("stationid");
		String questionkind=this.getPara("questionkind");
		if (startDate == null || startDate.trim().equals("")) {
			startDate = DateUtil.getToday();
		}
		if (endDate == null || endDate.trim().equals("")) {
			endDate = DateUtil.getToday();
		}
		List<Record> nList=ReturnBill.dao.findReturnListByStation(startDate, endDate, stationid);
		int totalnums=0,totalccnums=0,totalzjnums=0,temp=0;
		double totalamounts=0,totalccamounts=0,totalzjamounts=0;
		if(nList!=null && nList.size()>0){
			for(Record rec:nList){
				rec.set("ordernums", rec.getStr("ordernums").replaceAll("件", ""));
				rec.set("orderamounts", rec.getStr("orderamounts").replaceAll("￥", ""));
				rec.set("ccnums", rec.getStr("ccnums").replaceAll("件", ""));
				rec.set("ccamounts", rec.getStr("ccamounts").replaceAll("￥", ""));
				rec.set("zjnums", rec.getStr("zjnums").replaceAll("件", ""));
				rec.set("zjamounts", rec.getStr("zjamounts").replaceAll("￥", ""));
				if(rec.getStr("station_name")!=null && !rec.getStr("station_name").equals("站点合计")){
					totalnums=totalnums+Integer.valueOf(rec.get("ordernums").toString());
					totalamounts=totalamounts+Double.valueOf(rec.get("orderamounts").toString());
					totalccnums=totalccnums+Integer.valueOf(rec.get("ccnums").toString());
					totalccamounts=totalccamounts+Double.valueOf(rec.get("ccamounts").toString());
					totalzjnums=totalzjnums+Integer.valueOf(rec.get("zjnums").toString());
					totalzjamounts=totalzjamounts+Double.valueOf(rec.get("zjamounts").toString());
					rec.set("station_name", rec.getStr("station_name")+"\r\n（站长："+rec.getStr("username")+" 手机号："+rec.getStr("mobile")+"）");
				}
			}
			DecimalFormat df=new DecimalFormat("#0.0");
			Record r=new Record();
			r.set("station_name", "汇总合计").set("order_date", "").set("ordernums",totalnums+"").set("orderamounts", totalamounts+"").set("ccnums", totalccnums+"");
			r.set("ccamounts", totalccamounts+"").set("ccrate", df.format(totalccamounts*100/totalamounts)+"%").set("zjnums", totalzjnums+"");
			r.set("zjamounts", totalzjamounts+"").set("zjrate", df.format(totalzjamounts*100/totalamounts)+"%");
			nList.add(r);
			
		}
		String[] header = { "服务站","退赔订单日期", "总销售件数","总销售金额","残次退赔","残次退赔","残次退赔","整件退赔","整件退赔","整件退赔" };
		String[] header1 = { "服务站","退赔订单日期", "总销售件数","总销售金额","退赔件数","退赔金额","退赔率","退赔件数","退赔金额","退赔率" };
		String[] columns={"station_name","order_date","ordernums","orderamounts","ccnums","ccamounts","ccrate","zjnums","zjamounts","zjrate"};
		 // 创建工作薄  (换行 /r/n)
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();  
        // sheet:一张表的简称  
        // row:表里的行  
        // 创建工作薄中的工作表  
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("returnlist");
        // 单元格样式：正文内容
        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); 
        HSSFFont f=hssfWorkbook.createFont();
        f.setFontName("微软雅黑");
        f.setFontHeightInPoints((short)9);
        cellStyle.setFont(f);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setWrapText(true);//先设置为自动换行
        // 单元格样式：加粗显示
        HSSFCellStyle cellStyleBold = hssfWorkbook.createCellStyle(); 
        HSSFFont bf=hssfWorkbook.createFont();
        bf.setFontName("微软雅黑");
        bf.setFontHeightInPoints((short)9);
        bf.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cellStyleBold.setFont(bf);        
        cellStyleBold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyleBold.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleBold.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyleBold.setWrapText(true);//先设置为自动换行
        // 用于格式化单元格的数据
//        HSSFDataFormat format = hssfWorkbook.createDataFormat();
        // 创建行  
        HSSFRow row = hssfSheet.createRow(0);
        row.setHeightInPoints(27);  
        // 创建单元格，设置表头 创建列  
        HSSFCell cell = null;  
        //便利表头  
        for (int i = 0; i < header.length; i++) {  
            //创建传入进来的表头的个数  
            cell = row.createCell(i);  
            //表头的值就是传入进来的值  
            cell.setCellValue(header[i]);
            cell.setCellStyle(cellStyleBold);	// 为单元格添加样式
        }
        row = hssfSheet.createRow(1);
        row.setHeightInPoints(27);   
        //便利表头  
        for (int i = 0; i < header1.length; i++) {  
            //创建传入进来的表头的个数  
            cell = row.createCell(i);  
            //表头的值就是传入进来的值  
            cell.setCellValue(header1[i]);
            cell.setCellStyle(cellStyleBold);	// 为单元格添加样式
        } 
        // 设置列宽
        hssfSheet.setColumnWidth(0, 30 * 256); 
        hssfSheet.setColumnWidth(1, 18 * 256);
        hssfSheet.setColumnWidth(2, 18 * 256); 
        hssfSheet.setColumnWidth(3, 18 * 256);
        hssfSheet.setColumnWidth(4, 12 * 256);
        hssfSheet.setColumnWidth(5, 12 * 256);
        hssfSheet.setColumnWidth(6, 12 * 256);
        hssfSheet.setColumnWidth(7, 12 * 256);
        hssfSheet.setColumnWidth(8, 12 * 256);
        hssfSheet.setColumnWidth(9, 12 * 256);
        cellStyle.setFont(f);
        try { 
        	int count=1;
        	boolean isCellTotal=false;
            if(nList!=null && nList.size()>0){
            	for(Record rec:nList){
            		row = hssfSheet.createRow(++count);
            		row.setHeightInPoints(27);
            		isCellTotal=(rec.getStr("station_name")!=null && (rec.getStr("station_name").equals("站点合计") || rec.getStr("station_name").equals("汇总合计")));
            		for (int i = 0; i < header.length; i++) {           			
            			cell=row.createCell(i);
            			if(isCellTotal) cell.setCellStyle(cellStyleBold);
            			else cell.setCellStyle(cellStyle);
//            			cell.setCellStyle(cellStyle);	// 为单元格添加样式
//            			cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString());
            			if(i==2 || i==3 || i==4 || i==5 || i==7 || i==8){
            				try {
    							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//
    							cell.setCellValue(Double.valueOf(rec.get(columns[i]).toString()));
    						} catch (Exception e) {
    							e.printStackTrace();
    						} 
            			}else{
            				cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString());
            			}		
            		}
            	}
            }  
            
            // 设置合并单元格
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 6));
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 9));
            if(nList!=null && nList.size()>1){
            	Record r1=nList.get(0);
            	int startIndex=2;	// 0+1
            	int endIndex=2;
            	for(int i=1,size=nList.size();i<size;i++){
            		Record r2=nList.get(i);
            		if(!r2.getStr("station_name").equals(r1.getStr("station_name"))){
            			endIndex=i+1;
            			hssfSheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, 0, 0));            			
            			startIndex=i+2;
            		}
            		r1=nList.get(i);
            	}
            }
            
            HttpServletRequest request=this.getRequest();
            HttpServletResponse response=this.getResponse();
//            String filename="order.xls"; 
            String filename="退赔报表(按站点)"+DateUtil.getDateTime("yyyyMMddHHmmss", new Date())+".xls";
            response.setContentType("application/vnd.ms-excel");   
            response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "iso8859-1")); //设置文件头编码方式和文件名
            OutputStream ouputStream = response.getOutputStream();   
            hssfWorkbook.write(ouputStream);   
            ouputStream.flush();   
            ouputStream.close();  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }
	}
	
}

